
#region USING
using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
#endregion

namespace GeneralClasses
{
    #region SUMMARY
    ///holds payGrade and keeps track of it in the database
    #endregion

    #region NOTES
    /*
     * 3 NOV 2010 - cleaning it up. should probably update this note section more often. 
     * 13 OCT 2010 - Created class, cant put it in so nothing is tested, still writing in notepadd++.
     */

    #endregion

    public class PayGrade
    {

        #region VARIABLES
        //==============================================================================
        //                                                                     VARIABLES
        //==============================================================================

        String name;
        int payGradeID;
        float wage;

        DatabaseConnection dbc;

        #endregion

        #region PROPERTIES
        //==============================================================================
        //                                                                    PROPERTIES
        //==============================================================================

        /*public String ConnectionString
        {
            get { return dbc.ConnectionString; }
            set { dbc.ConnectionString = value; }
        }*/

        public float Wage
        {
            get { return wage; }
            set { wage = value; }
        }

        public int PayGradeID
        {
            get { return payGradeID; }
            set { payGradeID = value; }
        }

        public String Name
        {
            get { return name; }
            set { name = value; }
        }

        #endregion

        #region CONSTRUCTOR
        //==============================================================================
        //                                                                   CONSTRUCTOR
        //==============================================================================

        public PayGrade()
        {
            payGradeID = -1;
            name = "99";
            wage = 7.5f;
            dbc = new DatabaseConnection();
        }

        #endregion

        #region PRIVATE

        //==============================================================================
        //                                                                       PRIVATE
        //==============================================================================

        private int removePayGradeRelations()
        {
            try
            {
                if (payGradeID != -1)
                {
                    dbc.executeNonQuery("DELETE FROM personhaspaygraderelationship WHERE paid='" + this.payGradeID + "'");
                    return 0;
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private bool checkpayGradeUsed()
        {
            try
            {
                if (payGradeID != -1)
                {
                    DataTable table = dbc.query("SELECT * FROM personhaspaygraderelationship WHERE paid='" + this.payGradeID + "'");
                    if ((table != null) && table.Rows.Count > 0) { return true; }

                    table = dbc.query("SELECT * FROM paygradeneedsskillrelationship WHERE paid='" + this.payGradeID + "'");
                    if ((table != null) && table.Rows.Count > 0) { return true; }
                }
                return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        private bool checkPayGradeExists()
        {
            try
            {
                DataTable table = dbc.query("SELECT paid FROM Paygrade WHERE name = '" + name + "' AND num = '" + wage + "'");

                return ((table != null) && table.Rows.Count > 0) ? (true) : (false);
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private int findPayGradeID()
        {
            try
            {
                DataTable table = dbc.query("SELECT paid FROM Paygrade WHERE name = '" + name + "' AND num = '" + wage + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        return (int)row["paid"];
                    }
                    return -1;
                }
                else return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        #endregion

        #region PUBLIC
        //==============================================================================
        //                                                                        PUBLIC
        //==============================================================================

        public static List<PayGrade> getAllPayGrades()
        {
            try
            {
                DatabaseConnection dbc = new DatabaseConnection();
                List<PayGrade> paygrades = new List<PayGrade>();
                DataTable table = dbc.query("SELECT paid FROM Paygrade");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        PayGrade paygrade = new PayGrade();
                        paygrade.payGradeID = (int)row["paid"];
                        paygrade.loadData();
                        paygrades.Add(paygrade);
                    }
                    return paygrades;
                }
                else return null;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public static PayGrade loadData(int payGradeID)
        {
            PayGrade payGrade = new PayGrade();
            payGrade.PayGradeID = payGradeID;
            payGrade.loadData();
            return payGrade;
        }

        public void loadData()
        {
            try
            {
                DataTable table = dbc.query("SELECT * FROM PayGrade WHERE paid = '" + payGradeID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        /*
                         * UTIL.sayDebug("fields: " + reader.FieldCount.ToString());
                        UTIL.sayDebug("field type (1): " + reader.GetFieldType(1));
                        UTIL.sayDebug("field type (2): " + reader.GetFieldType(2));
                        UTIL.sayDebug("get float: " + reader.GetDouble(2).ToString());
                         */
                        this.name = (string)row["name"];
                        this.wage = (float)(double)row["num"]; // for some reason, even if i told it to be a float, it stores as a double
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public int saveData()
        {
            try
            {
                if (this.payGradeID == -1)
                {
                    if (!this.checkPayGradeExists())
                    {
                        dbc.executeNonQuery("INSERT INTO Paygrade (name, num) values ('" + name + "','" + wage + "')");
                    }
                    payGradeID = findPayGradeID();
                }
                else
                {
                    //save
                    dbc.executeNonQuery("UPDATE Paygrade SET name='" + name + "', num='" + wage.ToString() + "' WHERE paid='" + payGradeID + "'");
                }
                return payGradeID;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public int removeData()
        {
            try
            {
                if (payGradeID != -1)
                {
                    removePayGradeRelations();
                    if (!checkpayGradeUsed())
                    {
                        dbc.executeNonQuery("DELETE FROM PayGrade WHERE paid='" + this.payGradeID + "'");
                        return 0;
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public void deprecated_deletepayGrade() { }
        public void deprecated_addpayGrade() { }
        public int deprecated_getpayGradeID() { return -1; }

        #endregion

    } // end class


} //end namespace